<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<!-- vim: set encoding=utf-8: -->

<head>
    <title>simpleQL</title>
</head>

<body>
    <div id="header">
        <h1><a href="http://dealmeida.net/projects/simpleql/" title="Home">simpleQL</a></h1>
    </div>

    <div id="main">
        <div class="entry">
            <h2>What is this?</h2>

            <p>This module allows you to access a (DB API 2) SQL table using nothing but Python to build the query:</p>

<pre><code>&gt;&gt;&gt; from pysqlite2 import dbapi2 as sqlite
&gt;&gt;&gt; conn = sqlite.connect("coastline.db")

&gt;&gt;&gt; from simpleql.table import Table
&gt;&gt;&gt; table = Table(conn, "coastline", verbose=True)
&gt;&gt;&gt; print table
{'longitude': &lt;simpleql.table.Col object at 0x547070&gt;, 'latitude': &lt;simpleql.table.Col object at 0x547050&gt;}

&gt;&gt;&gt; for row in (r for r in table if r.latitude &gt; 83 and (r.longitude &lt; 300 or r.longitude &gt; 320)):
...     print row
<span style="color: blue;">SELECT longitude, latitude FROM coastline WHERE (latitude&gt;83) AND ((longitude&lt;300) OR (longitude&gt;320));</span>
{'longitude': 292.53553099999999, 'latitude': 83.016946000000004}
{'longitude': 292.188199, 'latitude': 83.019293000000005}
{'longitude': 290.23328500000002, 'latitude': 83.019293000000005}
{'longitude': 289.97044, 'latitude': 83.031026999999995}
{'longitude': 289.65127000000001, 'latitude': 83.014599000000004}
&lt;snip&gt;
</code></pre>

            <p>As you can see, the query string is built from a generator expression.  You can also use list comprehensions. Regular expressions are supported by the use of the <code>re.search</code> method:</p>

<pre><code>&gt;&gt;&gt; table = Table(conn, "table")
&gt;&gt;&gt; filtered = (r for r in table if re.search("string", r['column']))
&gt;&gt;&gt; for row in filtered:
...     print row
<span style="color: blue";>SELECT column FROM table WHERE column LIKE "%string%";</span>
&lt;snip&gt;
</code></pre>

            <p>The advantage of this approach over the <a href="http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/442447">similar recipe</a> is that if the (efficient) query builder fails when it encounters a complex filter the data will still be filtered (unefficiently) by the generator expression.</p>

            <h2>More information</h2>

            <p>Downloads can be made from the <a href="http://cheeseshop.python.org/pypi/simpleql/">Cheeseshop</a>.</p>

            <p class="break">❁</p>

        </div>
    </div>

    <div id="footer">
        <p>&copy; <a href="mailto:roberto@dealmeida.net">Roberto De Almeida</a> 2006</p>
    </div>
</body>

</html>
